﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;
using MySql.Data.MySqlClient;

namespace ExcelReadManager
{
    public class FileReadManager
    {
        private String filepath = String.Empty;
        private bool resultFlag = false;
        private String FALSE_DIALOG = "读取失败.....";
        private String TRUE_DIALOG = "读取成功.....";
        private String READING_DIALOG = "读取中.....";
        private String MYSQL_BEGIN = "开始链接数据库.....";
        private String MYSQL_TRUE = "连接成功.....";
        private String MYSQL_FALSE = "连接失败.....";
        private String MYSQL_INSERTING = "数据插入中.....";
        private String MYSQL_INSFALSE = "插入失败.....";
        private Form1 form1;
        public FileReadManager(Form1 frm)
        {
            this.form1 = frm;
            String path = String.Empty;
            OpenFileDialog of = new OpenFileDialog();
            of.Title = "请选择文件夹";
            of.Filter = "xls files(*.xls)|*.xls|xlsx files(*.xlsx)|*.xlsx";
            if(of.ShowDialog() == System.Windows.Forms.DialogResult.OK){
                path = of.FileName;
                if(path == String.Empty || path.Trim() == ""){
                    return;
                }else{
                    this.resultFlag = true;
                    this.filepath = path;
                }
            }
        }
        private MySqlConnection getMysqlCon(String localhost, String user_id, String password, String database)
        {
            String str_sqlcon = "server=" + localhost + ";user id=" + user_id + ";password=" + password + ";database=" + database + ";Charset=utf8";
            MySqlConnection con = new MySqlConnection(str_sqlcon);
            return con;
        }
        public void insertSql(DataTable dt)
        {
            MySqlConnection conn = getMysqlCon("127.0.0.1", "root", "", "dztest");
            String sql = "insert into populationinfo(cityName, maleNum, femaleNum, mfRa, urbanPoNum, ruralPoNum) values(@mycityName, @mymaleNum, @myfemaleNum, @mymfRa, @myurbanPoNum, @myruralPoNum)";
            if(dt != null){
                if (dt.Rows.Count > 0)
                {
                    try
                    {
                        conn.Open();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            MySqlParameter cityName = new MySqlParameter("@mycityName", MySqlDbType.VarChar);
                            cityName.Value = dt.Rows[i]["cityName"];
                            MySqlParameter maleNum = new MySqlParameter("@mymaleNum", MySqlDbType.VarChar);
                            maleNum.Value = dt.Rows[i]["maleNum"];
                            MySqlParameter femaleNum = new MySqlParameter("@myfemaleNum", MySqlDbType.VarChar);
                            femaleNum.Value = dt.Rows[i]["femaleNum"];
                            MySqlParameter mfRa = new MySqlParameter("@mymfRa", MySqlDbType.VarChar);
                            mfRa.Value = dt.Rows[i]["mfRa"];
                            MySqlParameter urbanPoNum = new MySqlParameter("@myurbanPoNum", MySqlDbType.VarChar);
                            urbanPoNum.Value = dt.Rows[i]["urbanPoNum"];
                            MySqlParameter ruralPoNum = new MySqlParameter("@myruralPoNum", MySqlDbType.VarChar);
                            ruralPoNum.Value = dt.Rows[i]["ruralPoNum"];
                            MySqlCommand comm = new MySqlCommand(sql, conn);
                            comm.Parameters.Add(cityName);
                            comm.Parameters.Add(maleNum);
                            comm.Parameters.Add(femaleNum);
                            comm.Parameters.Add(mfRa);
                            comm.Parameters.Add(urbanPoNum);
                            comm.Parameters.Add(ruralPoNum);
                            comm.ExecuteNonQuery();
                        }
                    }
                    catch (Exception mye)
                    {
                        MessageBox.Show(mye.Message);
                        conn.Close();
                        return;
                    }
                }
            }
            conn.Close();
        }
        public void insertEcnoSql(DataTable dt)
        {
            MySqlConnection conn = getMysqlCon("127.0.0.1", "root", "", "dztest");
            String sql = "insert into new_enconomicinfo(name, population, first_industry, second_industry, pubilc_revenue, public_expenditure, household_deposit, grain_yield, cotton_yield, oil_yield, meat_output, fixed_assets, fisedtel_number, secondary_schools, primary_schools, beds_healthycare) values(@name, @population, @first_industry, @second_industry, @pubilc_revenue, @public_expenditure, @household_deposit, @grain_yield, @cotton_yield, @oil_yield, @meat_output, @fixed_assets, @fisedtel_number, @secondary_schools, @primary_schools, @beds_healthycare)";
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    try
                    {
                        conn.Open();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            MySqlParameter name = new MySqlParameter("@name", MySqlDbType.VarChar);
                            name.Value = dt.Rows[i]["name"];
                            MySqlParameter population = new MySqlParameter("@population", MySqlDbType.VarChar);
                            population.Value = dt.Rows[i]["population"];
                            MySqlParameter first_industry = new MySqlParameter("@first_industry", MySqlDbType.VarChar);
                            first_industry.Value = dt.Rows[i]["first_industry"];
                            MySqlParameter second_industry = new MySqlParameter("@second_industry", MySqlDbType.VarChar);
                            second_industry.Value = dt.Rows[i]["second_industry"];
                            MySqlParameter pubilc_revenue = new MySqlParameter("@pubilc_revenue", MySqlDbType.VarChar);
                            pubilc_revenue.Value = dt.Rows[i]["pubilc_revenue"];
                            MySqlParameter public_expenditure = new MySqlParameter("@public_expenditure", MySqlDbType.VarChar);
                            public_expenditure.Value = dt.Rows[i]["public_expenditure"];
                            MySqlParameter household_deposit = new MySqlParameter("@household_deposit", MySqlDbType.VarChar);
                            household_deposit.Value = dt.Rows[i]["household_deposit"];
                            MySqlParameter grain_yield = new MySqlParameter("@grain_yield", MySqlDbType.VarChar);
                            grain_yield.Value = dt.Rows[i]["grain_yield"];
                            MySqlParameter cotton_yield = new MySqlParameter("@cotton_yield", MySqlDbType.VarChar);
                            cotton_yield.Value = dt.Rows[i]["cotton_yield"];
                            MySqlParameter oil_yield = new MySqlParameter("@oil_yield", MySqlDbType.VarChar);
                            oil_yield.Value = dt.Rows[i]["oil_yield"];
                            MySqlParameter meat_output = new MySqlParameter("@meat_output", MySqlDbType.VarChar);
                            meat_output.Value = dt.Rows[i]["meat_output"];
                            MySqlParameter fixed_assets = new MySqlParameter("@fixed_assets", MySqlDbType.VarChar);
                            fixed_assets.Value = dt.Rows[i]["fixed_assets"];
                            MySqlParameter fisedtel_number = new MySqlParameter("@fisedtel_number", MySqlDbType.VarChar);
                            fisedtel_number.Value = dt.Rows[i]["fisedtel_number"];
                            MySqlParameter secondary_schools = new MySqlParameter("@secondary_schools", MySqlDbType.VarChar);
                            secondary_schools.Value = dt.Rows[i]["secondary_schools"];
                            MySqlParameter primary_schools = new MySqlParameter("@primary_schools", MySqlDbType.VarChar);
                            primary_schools.Value = dt.Rows[i]["primary_schools"];
                            MySqlParameter beds_healthycare = new MySqlParameter("@beds_healthycare", MySqlDbType.VarChar);
                            beds_healthycare.Value = dt.Rows[i]["beds_healthycare"];
                            MySqlCommand comm = new MySqlCommand(sql, conn);
                            comm.Parameters.Add(name);
                            comm.Parameters.Add(population);
                            comm.Parameters.Add(first_industry);
                            comm.Parameters.Add(second_industry);
                            comm.Parameters.Add(pubilc_revenue);
                            comm.Parameters.Add(public_expenditure);
                            comm.Parameters.Add(household_deposit);
                            comm.Parameters.Add(grain_yield);
                            comm.Parameters.Add(cotton_yield);
                            comm.Parameters.Add(oil_yield);
                            comm.Parameters.Add(meat_output);
                            comm.Parameters.Add(fixed_assets);
                            comm.Parameters.Add(fisedtel_number);
                            comm.Parameters.Add(secondary_schools);
                            comm.Parameters.Add(primary_schools);
                            comm.Parameters.Add(beds_healthycare);
                            comm.ExecuteNonQuery();
                        }
                    }
                    catch (Exception mye)
                    {
                        MessageBox.Show(mye.Message);
                        conn.Close();
                        return;
                    }
                }
            }
            conn.Close();
        }
        public DataTable readFile(String path)
        {
            DataTable dt = new DataTable();
            try
            {
                form1.reading.Text = READING_DIALOG;
                //连接字符串
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本
                using (OleDbConnection olc = new OleDbConnection(connstring))
                {
                    olc.Open();
                    DataTable sheetName = olc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });//得到所有sheet的名字
                    string firstSheetName = sheetName.Rows[0][2].ToString();//拿到第一个sheet的名字
                    string sql = string.Format("SELECT trim(f1) as cityName,trim(f3) as maleNum,trim(f4) as femaleNum,trim(f5) as mfRa,trim(f9) as urbanPoNum,trim(f10) as ruralPoNum FROM [{0}] where f3 like '%[0-9]%' and f1 not like '%市辖区%' and f1 not like '县' and f1 not like '%省'", firstSheetName); //查询字符串
                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);
                    dt = set.Tables[0];
                }
            }
            catch (Exception e)
            {
                form1.reading.Text = FALSE_DIALOG;
                MessageBox.Show(e.Message);
                dt = null;
                return dt;
            }
            form1.reading.Text = TRUE_DIALOG;
            return dt;
        }
        public DataTable readEncoMes(String path)
        {
            DataTable dt = new DataTable();
            try
            {
                form1.reading.Text = READING_DIALOG;
                //连接字符串
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本
                using (OleDbConnection olc = new OleDbConnection(connstring))
                {
                    olc.Open();
                    DataTable sheetName = olc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });//得到所有sheet的名字
                    string firstSheetName = sheetName.Rows[0][2].ToString();//拿到第一个sheet的名字
                    string sql = string.Format("SELECT trim(f1) as name, trim(f4) as population, trim(f5) as first_industry, trim(f6) as second_industry, trim(f7) as pubilc_revenue, trim(f8) as public_expenditure, trim(f9) as household_deposit, trim(f10) as grain_yield, trim(f11) as cotton_yield, trim(f12) as oil_yield, trim(f13) as meat_output, trim(f14) as fixed_assets, trim(f15) as fisedtel_number, trim(f16) as secondary_schools, trim(f17) as primary_schools, trim(f18) as beds_healthycare FROM [{0}] where trim(f3) like '%[0-9]%' and trim(f1) not like '%省' ", firstSheetName); //查询字符串
                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);
                    dt = set.Tables[0];
                
                }
            }
            catch (Exception e)
            {
                form1.reading.Text = FALSE_DIALOG;
                MessageBox.Show(e.Message);
                dt = null;
                return dt;
            }
            form1.reading.Text = TRUE_DIALOG;
            return dt;
        }
        public bool getResultDialog{
            get
            {
                return this.resultFlag;
            }
        }
        public String getFilePath
        {
            get
            {
                return this.filepath;
            }
        }
    }
}
